Imports System.Data
Imports System.Data.SqlClient
Imports MyClasses
Public Class DPOrder

    Public Shared Function GetReorderItem(ByVal vendorid As Integer) As DataTable
        Dim StrSql As String
        Try
            StrSql = "select mpItem.itemId,itemName, CurrentStock, mpItem.maxReorderQty, mpItem.purUnit, mpItem.purPrice" & _
                     " from tRelation, mPItem, tStockSummary " & _
                     " where tRelation.vendorID = " & vendorid & "And tRelation.itemID = tStockSummary.itemID And CurrentStock <= MinReorderLevel " & _
                     " and mPItem.itemID = tRelation.itemID"
            Return DbHelper.ExecuteDataSet(StrSql).Tables(0)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPOrder", "GetReorderItem")
            Return Nothing
        End Try
    End Function

    Public Shared Function Save(ByRef POrder As CPOrder, ByVal SaveUpdate As String, ByRef PorderItem() As CBillItem) As Integer
        Dim StrSql As String
        Dim param(8) As SqlParameter
        Try
            If SaveUpdate = "S" Then
                StrSql = "INSERT INTO tPurchaseOrder(PONo, SeqPONo,PODate,VendorID," & _
                "DeliveryDate,Amount,Remarks ,LU,LD)" & _
                "VALUES(@PONo,@SeqPONo,@PODate,@VendorID," & _
                "@DeliveryDate,@Amount,@Remarks,@LU,@LD)"
                POrder.PoID = DGeneral.GetSeqNum("PO")
                param(1) = New SqlParameter("@SeqPONo", DGeneral.GetSeqNum("PO") & POrder.PoID.ToString())
            Else
                StrSql = "UPDATE  tPurchaseOrder SET PODate= @PODate, VendorID= @VendorID , DeliveryDate=@DeliveryDate,Amount=@Amount," & _
                "Remarks =Remarks,LU=@LU,LD=@LD WHERE PONo=@PONo "
                param(1) = New SqlParameter("@SeqPONo", IIf(IsNothing(POrder.PoSeqID), 0, POrder.PoSeqID))
            End If

            param(0) = New SqlParameter("@PONo", POrder.PoID)
            'param(2) = New SqlParameter("@SeqPONo", POrder.PoSeqID)
            param(2) = New SqlParameter("@PODate", POrder.poDate)
            param(3) = New SqlParameter("@VendorID", POrder.vendorID)
            param(4) = New SqlParameter("@DeliveryDate", POrder.delDate)
            param(5) = New SqlParameter("@Amount", POrder.totalAmt)
            param(6) = New SqlParameter("@Remarks", POrder.Remarks)
            ' param(7) = New SqlParameter("@Status", "A")
            param(7) = New SqlParameter("@LU", CCommon.GetUserID)
            param(8) = New SqlParameter("@LD", Date.Now())
            Call savePOrderItem(POrder.PoID, SaveUpdate, PorderItem)
            Return DbHelper.ExecuteNonQuery(StrSql, param)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPOrder", "Save")
            Return 0
        End Try

    End Function
    Public Shared Function savePOrderItem(ByVal POID As Integer, ByVal SaveUpdate As String, ByRef PorderItem() As CBillItem) As Integer
        Dim StrSql As String
        Dim param(6) As SqlParameter
        Try
            For Each POitem As CBillItem In PorderItem
                If SaveUpdate = "S" Then
                    StrSql = "INSERT INTO tPurchaseOrderDet (PONo, ItemID, Item_Unit, qty, Price, CanWait, Remark )" & _
                             " VALUES(@PONo, @ItemID, @Item_Unit, @qty, @Price, @CanWait, @Remark)"
                Else
                    StrSql = "UPDATE tPurchaseOrderDet" & _
                             " SET PONo= @PONo, ItemID= @ItemID, Item_Unit= @Item_Unit, qty= @qty, Price= @Price, CanWait= @CanWait, Remark= @Remark " & _
                             " WHERE PONo= @PONo AND ItemID= @ItemID"
                End If
                With POitem
                    param(0) = New SqlParameter("@PONo", POID)
                    param(1) = New SqlParameter("@ItemID", .ItemID)
                    param(2) = New SqlParameter("@Item_Unit", .Unit)
                    param(3) = New SqlParameter("@qty", .Quantity)
                    param(4) = New SqlParameter("@Price", .price)
                    param(5) = New SqlParameter("@CanWait", .CanWait)
                    param(6) = New SqlParameter("@Remark", .Remark)
                End With
                DbHelper.ExecuteNonQuery(StrSql, param)
            Next
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPOrder", "savePOrderItem")
        End Try

    End Function
    Public Shared Function SearchPorder(ByVal PorderID As Integer) As DataTable
        Dim StrSql As String
        Try
            StrSql = "SELECT SeqPONo,VendorID, PODate, DeliveryDate, Amount, Remarks FROM tPurchaseOrder WHERE PONo =" & PorderID
            Return DbHelper.ExecuteDataTable(StrSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPOrder", "SearchPorder")
            Return Nothing
        End Try
    End Function
    Public Shared Function SearchPorderItem(ByVal Porderid As Integer) As DataTable
        Try
            Dim StrSql As String
            StrSql = "SELECT mpItem.ItemID, mpItem.itemName,  sum(quantity) as Stock, Qty, CodeDesc, Price, CanWait, tPurchaseOrderDet.Remark" & _
                     " FROM tPurchaseOrderDet, mpItem, MMisCCd, tStock" & _
                     " WHERE tPurchaseOrderDet.itemID = mpItem.itemID AND PONo =" & Porderid & " AND CodeID = Item_Unit AND  tStock.itemID = tPurchaseOrderDet.itemID" & _
                     " GROUP BY mpItem.ItemID, mpItem.itemName, CodeDesc, Qty, Price, CanWait, tPurchaseOrderDet.Remark"
            Return DbHelper.ExecuteDataTable(StrSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPOrder", "SearcPorderItem")
            Return Nothing
        End Try
    End Function


End Class
